﻿Imports System
Imports System.Data
Imports System.IO
Imports System.Text

Public Class frmBroker
    Dim Strsql As String
    Dim Mysql1 As New ClsSQLhelper
    Dim StrMssql As String
    Dim Mssql1 As New ClsSQLhelper
    Dim iQMysql As New ClsSQLhelper
    '---------------
    Const ver As String = "iQPACs Broker License For AKISSOFT-Inburi Hospital-"
    Private cnPACS As String = ""
    Private tt As New myTTranslite_TypeLib.TTransliteCtrl

    Private Sub MySQLConfigToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MySQLConfigToolStripMenuItem.Click
        frmMySQLSetting.Show()
    End Sub


    Private Sub MsSQLConfigToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MsSQLConfigToolStripMenuItem.Click
        frmMsSQLSetting.Show()
    End Sub

    Private Sub LicenseConfigToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LicenseConfigToolStripMenuItem.Click
        frmLicense.Show()
    End Sub

    Private Sub IQMySQLConfigToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles IQMySQLConfigToolStripMenuItem.Click
        frmiQMySQLSetting.Show()
    End Sub

    Public Function uuid() As String
        Dim sSql As String
        Dim DSs As New DataSet
        Dim Mysql1 As New ClsSQLhelper
        Dim uuids As String
        sSql = "select uuid()  "

        DSs = Mysql1.GetMYSQLDataset(sSql, "ds_sql", "xray_report")

        If DSs.Tables(0).Rows.Count > 0 Then

            uuids = "{" & DSs.Tables(0).Rows(0).Item(0).ToString & "}"

        End If
        Return uuids
    End Function

    Private Sub cmdStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStart.Click
        Me.txtStatus.Text = ver & " Running .." & Format(Now, "dd/MM/yyyy hh:mm:ss")
        Me.txtConsole.Text = " Begin ..." & Format(Now, "dd/MM/yyyy hh:mm:ss") & vbNewLine & Me.txtConsole.Text
        Me.Timer1.Interval = Val(Me.txtInterval.Text) * 1000
        Me.Timer1.Enabled = True

        Me.cmdStart.Enabled = False
        Me.cmdStop.Enabled = True

    End Sub

    Private Sub cmdStop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStop.Click
        Me.txtStatus.Text = "Sleeping....zzzz"
        Me.txtConsole.Text = " End ... " & Format(Now, "dd/MM/yyyy hh:mm:ss") & vbNewLine & Me.txtConsole.Text
        Me.txtLastXN.Text = ""
        Me.Timer1.Enabled = False

        Me.cmdStart.Enabled = True
        Me.cmdStop.Enabled = False

    End Sub


    Private Sub cmdMy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMy.Click

        '*** DataTable ***' 
        Dim DS As New DataSet
        Dim getdate As String = MsDate(Date.Now)
        'Strsql = "select * from sys_var  where sys_name like 'PAC%'"
        If chkCustomQuery.Checked Then
            If txtQuery.Text = "" Then
                MsgBox("กรุณาป้อน Query ที่ต้องการค้นหาข้อมูล (MySQL)", MsgBoxStyle.Information)
                Exit Sub
            Else
                Strsql = txtQuery.Text
            End If
        Else
            Strsql = "select" & _
                   " x.xn as ACCESSIONNUMBER, " & _
                   "  '' as MODALITY," & _
                   " '' as AETITLES ," & _
                   " x.hn as PATIENT_ID, " & _
                   "  if(x.an<>'' ,'IPD','OPD') as PATIENTTYPE," & _
                   "  x.xn as CLINICAL_ID, " & _
                   "  concat(p.pname,p.fname,'-',p.lname)   as FIRSTNAME," & _
                   "  p.lname as LASTNAME," & _
                   "  p.sex as PATIENTSEX," & _
                   "  h.age_y as PATIENTAGE," & _
                   "  p.birthday as BIRTHDATE," & _
                   "  4 as BEDID," & _
                   " '' as PATIENTSIZE, " & _
                   " '' as PATIENTWEIGHT, " & _
                   " p.hometel as PATIENTTELEPHONENUMBER, " & _
                   "  x.request_date as ADMITTINGDATE," & _
                   "  CONCAT('[' , i.xray_items_name , ']') as REQUESTEDPROCEDUREDESCRIPTION," & _
                   "  CONCAT('[' , i.xray_items_name , ']') as EXAMINEPART," & _
                   "  1 as PART_COUNT," & _
                   "  i.service_price as EXAMINEFEE," & _
                   "  0 as EXAMINEDISCOUNT," & _
                   "  x.xn as CASE_ID," & _
                   "  x.xray_priority_id as PRIORITY," & _
                   "  SUBSTRING(TRIM(d.name),1,20) as REGISTEROR," & _
                   "  x.request_date as UPLOAD_DATE," & _
                   "  x.request_date as `START_REGISTERDATE`," & _
                   "  x.request_date as `REGISTERDATE`," & _
                   " '0' as `FILM1417`," & _
                   "  x.request_date as `START_EXAMINEDATE`," & _
                   "  x.request_date as `EXAMINEDATE`," & _
                   " 'F' as `DIAGNOSISRESULT`," & _
                   "  'T' as `CLINIC_ACCORD`," & _
                   "  SUBSTRING(TRIM(d2.name),1,20) as `REPORTDOCTOR`," & _
                   "  x.request_date as `START_REPORTDATE`," & _
                   "  x.request_date as `REPORTDATE`," & _
                   "  x.request_date as `START_AUDITINGDATE`," & _
                   "  x.request_date as `AUDITINGDATE`," & _
                   "  x.request_date as `AUDITINGDATE1`," & _
                   " '1' as `PROF`," & _
                   " '11266' as `HOSPITAL_ID`," & _
                   " '2' as `BSTATE`," & _
                   "  x.request_date as `LASTUPDATEDATE`," & _
                   "  x.request_date as `LASTPRINTDATE`," & _
                   "  CONCAT('[' , i.xray_items_name , ']') as STUDIESMEMO," & _
                   "  SUBSTRING(TRIM(k.department),1,40) as REQUESTINGDEPARTMENT," & _
                   "  SUBSTRING(TRIM(d.name),1,20) as REFERINGPHYSICIAN," & _
                   "  n.name as `NATION`, " & _
                   "  x.xn as xn," & _
                   "  x.vn as vn, " & _
                   "  x.xray_room_id as room," & _
                   "  p.sex," & _
                   "  i.xray_items_group " & _
                   "  from xray_report x " & _
                   "  left join xray_room r  on x.xray_room_id = r.xray_room" & _
                   "  left join patient p  on x.hn = p.hn" & _
                   "  left join xray_items i   on x.xray_items_code = i.xray_items_code" & _
                   "  left join xray_head h  on x.vn = h.vn" & _
                   "  left join doctor d    on x.request_doctor = d.code" & _
                   "  left join doctor d2  on x.report_doctor = d2.code" & _
                   "  left join kskdepartment k  on  k.depcode = x.request_depcode" & _
                   "  left join nationality n on n.nationality = p.nationality " & _
                   "  where request_date =  '" & getdate & "' order by x.xn desc "

        End If



        DS = Mysql1.GetMYSQLDataset(Strsql, "DS_onlineuser", "sys_var")

        DataGridViewPACs.DataSource = DS.Tables(0)

    End Sub

    Private Sub cmdMs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdMs.Click
        '*** DataTable ***' 
        Dim DS As New DataSet
        Dim getdate As String = MsDate(Date.Now)

        If chkCustomQuery.Checked Then
            If txtQuery.Text = "" Then
                MsgBox("กรุณาป้อน Query ที่ต้องการค้นหาข้อมูล (MsSQL)", MsgBoxStyle.Information)
                Exit Sub
            Else
                StrMssql = txtQuery.Text
            End If

        Else
            StrMssql = "SELECT * FROM RIS_STUDIES  WHERE EXAMINEDATE ='" & getdate & "' ORDER BY ACCESSIONNUMBER DESC "
        End If



        DS = Mssql1.GetMSSQLDataset(StrMssql, "DS_RIS_STUDIES", "RIS_STUDIES")

        DataGridViewPACs.DataSource = DS.Tables(0)

    End Sub

    Private Sub CmdUTF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdUTF.Click
        Dim bytes As Byte()
        bytes = Encoding.ASCII.GetBytes("นันท์^พีชานนท์")
        Dim str As String
        str = System.Text.Encoding.UTF8.GetString(bytes)

        str = System.Text.UnicodeEncoding.UTF8.GetString(bytes)
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Dim cl As Double = 0
        Me.Timer1.Enabled = False

        cl = Val(Me.txtConsoleWord.Text)

        Dim str As String = Me.txtConsole.Text

        If Len(str) > cl Then
            Me.txtConsole.Text = Microsoft.VisualBasic.Left(str, cl)
        End If

        Me.txtStatus.Text = ver & " Running .." & Format(Now, "dd/MM/yyyy hh:mm:ss")

        DateTimePicker1.Value = Now()
        ' ''Me.txtConsole.Text = "HosXP 04 UTF-8 in " & Format(Now, "dd/MM/yyyy hh:mm:ss") & vbNewLine & Me.txtConsole.Text
        Me.GetData()
        Me.UpPacsReport2HOS()
        'DaySrearch()

        'Me.GetDataUTF()
        Me.Timer1.Enabled = True
    End Sub
    Private Function GetLastXN() As String


        ''*** DataTable ***' 
        'Dim DS As New DataSet

        'StrMssql = "SELECT MAX(ACCESSIONNUMBER)as MAX_ORDER FROM RIS_STUDIES  "

        'DS = Mssql1.GetMSSQLDataset(StrMssql, "DS_RIS_STUDIES", "RIS_STUDIES")
        ''DataGridViewPACs.DataSource = DS.Tables(0)
        'If DS.Tables(0).Rows.Count = 0 Then
        '    GetLastXN = 0
        'Else
        '    GetLastXN = DS.Tables(0).Rows(0).Item(0).ToString
        '    txtLastXN.Text = DS.Tables(0).Rows(0).Item(0).ToString
        'End If

        '*** DataTable ***' 
        Dim DS As New DataSet

        StrMssql = "select max(xn) from xn2acc"

        DS = Mssql1.GetMSSQLDataset(StrMssql, "DS_xn2acc", "xn2acc")
        'DataGridViewPACs.DataSource = DS.Tables(0)
        If DS.Tables(0).Rows.Count = 0 Then
            GetLastXN = 0
        Else
            GetLastXN = DS.Tables(0).Rows(0).Item(0).ToString
            txtLastXN.Text = DS.Tables(0).Rows(0).Item(0).ToString
        End If


    End Function
    Private Function GetHosXN() As String


        '*** DataTable ***' 
        Dim DS As New DataSet

        Strsql = "select max(xn)as xnmax from xray_report  "

        DS = Mysql1.GetMYSQLDataset(Strsql, "ds_xray_report", "xray_report")
        'DataGridViewPACs.DataSource = DS.Tables(0)
        If DS.Tables(0).Rows.Count = 0 Then
            GetHosXN = 0
        Else
            GetHosXN = DS.Tables(0).Rows(0).Item(0).ToString

        End If

    End Function

    Public Function HL7ReserverClear(ByVal str As String) As String
        str = Replace(str, "|", "")
        str = Replace(str, "~", "")
        str = Replace(str, "\", "")
        str = Replace(str, "/", "")
        str = Replace(str, "&", "")
        str = Replace(str, "'", "")
        str = Replace(str, """", "")
        str = Replace(str, "(", "")
        str = Replace(str, ")", "")
        str = Replace(str, "-", "")
        str = Replace(str, "1", "")
        str = Replace(str, "2", "")
        str = Replace(str, "3", "")
        str = Replace(str, "4", "")
        str = Replace(str, "5", "")
        str = Replace(str, "6", "")
        str = Replace(str, "7", "")
        str = Replace(str, "8", "")
        str = Replace(str, "9", "")
        str = Replace(str, "0", "")
        str = Replace(str, "ฯ", "")

        HL7ReserverClear = str
    End Function
    Private Shared Function ConvertToDate(ByVal dateVal As String, ByVal dateFormat As String, ByVal cultureName As String) As Date
        Dim returnDate As Date

        If Date.TryParseExact(dateVal, dateFormat, New System.Globalization.CultureInfo(cultureName, True), Globalization.DateTimeStyles.None, returnDate) Then
            Return returnDate
        End If

        Return Nothing
    End Function
    Public Function MsDate(ByVal strdate As Date) As String
        Dim strtime As String
        Dim aDate As String
        strtime = "00:00:00"
        ''aDate = Convert.ToDateTime(strdate).Day.ToString("00") & "-" +
        ''                        Convert.ToDateTime(strdate).Month.ToString("00") & "-" +
        ''                        Convert.ToDateTime(strdate).Year.ToString("0000")
        'aDate = Convert.ToDateTime(strdate).Year.ToString("0000") & "-" +
        '        Convert.ToDateTime(strdate).Month.ToString("00") & "-" +
        '        Convert.ToDateTime(strdate).Day.ToString("00")

        aDate = strdate.Year.ToString() + strdate.ToString("-MM-dd")

        Return aDate

    End Function
    Public Function iQDate(ByVal strdate As Date) As String
        Dim strtime As String
        Dim aDate As String
        strtime = "00:00:00"
        ''aDate = Convert.ToDateTime(strdate).Day.ToString("00") & "-" +
        ''                        Convert.ToDateTime(strdate).Month.ToString("00") & "-" +
        ''                        Convert.ToDateTime(strdate).Year.ToString("0000")
        'aDate = Convert.ToDateTime(strdate).Year.ToString("0000") & "-" +
        '        Convert.ToDateTime(strdate).Month.ToString("00") & "-" +
        '        Convert.ToDateTime(strdate).Day.ToString("00")

        'aDate = strdate.ToString("-dd-MM") + strdate.Year.ToString()
        aDate = strdate.Year.ToString() + strdate.ToString("-MM-dd")


        Return aDate

    End Function

    Public Function MsDateTime(ByVal strdate As Date, ByVal strtime As DateTime) As String

        Dim aDateTime As String
        'Dim y As Integer = Year(strdate)
        'If y > 2400 Then
        '    y = y - 543
        'End If
        ''MsDateTime = strdate & " " & Format(strtime, "hh:mm:ss")
        'MsDateTime = Trim(Str(y)) & "-" & Trim(Str(Month(strdate))) & "-" & " " & Format(Now, "HH:mm:ss")
        'aDateTime = Convert.ToDateTime(strdate).Day.ToString("00") & "/" +
        '                Convert.ToDateTime(strdate).Month.ToString("00") & "/" +
        '                Convert.ToDateTime(strdate).Year.ToString("0000") & " " & Format(strtime, "HH:mm:ss")
        'aDateTime = Convert.ToDateTime(strdate).Year.ToString("0000") & "-" +
        '            Convert.ToDateTime(strdate).Month.ToString("00") & "-" +
        '            Convert.ToDateTime(strdate).Day.ToString("00") & " " & Convert.ToDateTime(strtime).ToLongTimeString.ToString

        'Convert.ToDateTime(strdate).Day.ToString("00") & " " & Format(strtime, "hh:mm:ss")
        '& Format(strtime, "hh:mm:ss")

        aDateTime = strdate.Year.ToString() + strdate.ToString("-MM-dd") + " " & Format(strtime, "hh:mm:ss")

        Return aDateTime
    End Function
    Public Function ModalityGroup(ByVal strGroup As String) As String
        Dim Modal As String
        Select Case Trim(strGroup)

            Case "1"
                Modal = "CR"
                ' X-Ray
            Case "2"
                Modal = "US"
                'Ultrasound
            Case "3"
                Modal = "CT"
                ' CT
            Case "4"
                Modal = "MRI"
                'MRI
            Case "5"
                Modal = "MAM"
                ' Mammogram

        End Select
        Return Modal
    End Function
    Private Function GetName(ByVal patientID As String, ByVal fname As String, ByVal lname As String, ByVal pname As String) As String

        'If Me.chkThaiToEnglish.Checked Then
        '    GetName = TH2R(fname) & "^" & TH2R(lname)
        'Else
        '    GetName = Trim(pname) & Trim(fname) & "^" & Trim(lname)
        'End If

    End Function

    Private Function TH2R(ByVal s As String) As String
        Dim tp As String = Trim(Me.txtThaiRomanizationPath.Text)
        My.Computer.FileSystem.WriteAllText(tp & "\data.txt", Trim(s), False, System.Text.ASCIIEncoding.Default)
        tt.Initial(tp)
        tt.RomanUnicodeFile(tp & "\data.txt")
        TH2R = Trim(My.Computer.FileSystem.ReadAllText(tp & "\data.txt.rom"))
        TH2R = Replace(TH2R, " ", "")
        TH2R = Microsoft.VisualBasic.Left(TH2R, Len(TH2R) - 2)
    End Function
    Public Function xhosData(ByVal str As String) As String
        Try
            Dim DS As New DataSet
            Dim iQDS1 As New DataSet
            Dim iQstrSql1 As String
            Dim trans_fname As String
            Dim trans_lastname As String



            Dim xstr As String
            xstr = "select " & _
                     " x.xn as ACCESSIONNUMBER, " & _
                     "  '0' as MODALITY," & _
                     " x.hn as PATIENT_ID, " & _
                     "  if(x.an<>'' ,'IPD','OPD') as PATIENTTYPE," & _
                     "  x.xn as CLINICAL_ID, " & _
                     "  concat(p.pname,p.fname,'^',p.lname)   as FIRSTNAME," & _
                     "  concat(p.pname,p.fname,'^',p.lname) as LASTNAME," & _
                     "  if(p.sex =1,'M','F') as PATIENTSEX," & _
                     "  timestampdiff(year,p.birthday,now())  as PATIENTAGE," & _
                     "  p.birthday as BIRTHDATE," & _
                     "  4 as BEDID," & _
                     " '0' as PATIENTSIZE, " & _
                     " '0' as PATIENTWEIGHT, " & _
                     " p.hometel as PATIENTTELEPHONENUMBER, " & _
                     "  x.request_date as ADMITTINGDATE," & _
                     "  CONCAT('[' , i.xray_items_name , ']') as REQUESTEDPROCEDUREDESCRIPTION," & _
                     "  CONCAT('[' , i.xray_items_name , ']') as EXAMINEPART," & _
                     "  1 as PART_COUNT," & _
                     "  i.service_price as EXAMINEFEE," & _
                     "  0 as EXAMINEDISCOUNT," & _
                     "  x.xn as CASE_ID," & _
                     "  x.xray_priority_id as PRIORITY," & _
                     "  SUBSTRING(TRIM(d.name),1,20) as REGISTEROR," & _
                     "  x.request_date as UPLOAD_DATE," & _
                     "  x.request_date as `START_REGISTERDATE`," & _
                     "  x.request_date as `REGISTERDATE`," & _
                     " '0' as `FILM1417`," & _
                     "  x.request_date as `START_EXAMINEDATE`," & _
                     "  x.request_date as `EXAMINEDATE`," & _
                     " 'F' as `DIAGNOSISRESULT`," & _
                     "  'T' as `CLINIC_ACCORD`," & _
                     "  SUBSTRING(TRIM(d2.name),1,20) as `REPORTDOCTOR`," & _
                     "  x.request_date as `START_REPORTDATE`," & _
                     "  x.request_date as `REPORTDATE`," & _
                     "  x.request_date as `START_AUDITINGDATE`," & _
                     "  x.request_date as `AUDITINGDATE`," & _
                     "  x.request_date as `AUDITINGDATE1`," & _
                     " '1' as `PROF`," & _
                     " '11266' as `HOSPITAL_ID`," & _
                     " '2' as `BSTATE`," & _
                     "  x.request_date as `LASTUPDATEDATE`," & _
                     "  x.request_date as `LASTPRINTDATE`," & _
                     "  CONCAT('[' , i.xray_items_name , ']') as STUDIESMEMO," & _
                     "  SUBSTRING(TRIM(k.department),1,40) as REQUESTINGDEPARTMENT," & _
                     "  SUBSTRING(TRIM(d.name),1,20) as REFERINGPHYSICIAN," & _
                     "  n.name as `NATION`, " & _
                     "  x.xn as xn," & _
                     "  x.vn as vn, " & _
                     "  x.xray_room_id as room," & _
                     "  p.sex," & _
                     "  i.xray_items_group, " & _
                     "  x.request_time, " & _
                     "  p.pname,p.fname," & _
                     "  p.lname,p.sex,i.xray_items_name,LPAD(x.xray_items_code,4,0)AS XRAYITEMCODE,i.xray_items_code " & _
                     "  from xray_report x " & _
                     "  left join xray_head h  on x.vn = h.vn" & _
                     "  left join xray_items i   on x.xray_items_code = i.xray_items_code" & _
                     "  left join patient p  on x.hn = p.hn" & _
                     "  left join xray_room r  on x.xray_room_id = r.xray_room" & _
                     "  left join doctor d    on x.request_doctor = d.code" & _
                     "  left join doctor d2  on x.report_doctor = d2.code" & _
                     "  left join kskdepartment k  on  k.depcode = x.request_depcode" & _
                     "  left join nationality n on n.nationality = p.nationality " & _
                     "  where x.xn='" & str & "' "

            DS = Mysql1.GetMYSQLDataset(xstr, "ds_xray_report", "xray_report")

            If DS.Tables(0).Rows.Count > 0 Then
                'Trans Thai To Eng 
                If chkThaiToEnglish.Checked Then

                    trans_lastname = UCase(TH2R(DS.Tables(0).Rows(0).Item("fname").ToString()) & "^" & TH2R(DS.Tables(0).Rows(0).Item("lname").ToString()))
                Else
                    trans_lastname = DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString()
                End If

                If chkEncode.Checked Then
                    trans_fname = AsciiToUtf8(DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString())
                    trans_lastname = AsciiToUtf8(DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString())

                    'utf_lname = UText(DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString())
                    'utf_lastname = UText(DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString())

                    'utf_lname = AsciiToUtf8(DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString())
                    'utf_lastname = AsciiToUtf8(DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString())


                Else
                    trans_fname = DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString()
                    'trans_lastname = DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString()
                End If


                'aa = MsDate(DS.Tables(0).Rows(0).Item("BIRTHDATE").ToString())
                'aa = ConvertToDate(DS.Tables(0).Rows(0).Item("BIRTHDATE").ToString(), "dd/MM/yyyy hh:mm:ss", "en-US")
                xhosData = " '" & DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString() & "' ," & _
                          " '" & ModalityGroup(DS.Tables(0).Rows(0).Item("xray_items_group").ToString()) & "', " & _
                          " '" & DS.Tables(0).Rows(0).Item("PATIENT_ID").ToString() & "', " & _
                          " '" & DS.Tables(0).Rows(0).Item("PATIENTTYPE").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("CLINICAL_ID").ToString() & "', " & _
                          " '" & HL7ReserverClear(trans_fname) & "' ," & _
                          " '" & HL7ReserverClear(trans_lastname) & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PATIENTSEX").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PATIENTAGE").ToString() & "', " & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("BIRTHDATE").ToString()) & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("BEDID").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PATIENTSIZE").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PATIENTWEIGHT").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PATIENTTELEPHONENUMBER").ToString() & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("ADMITTINGDATE").ToString()) & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("REQUESTEDPROCEDUREDESCRIPTION").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("EXAMINEPART").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PART_COUNT").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("EXAMINEFEE").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("EXAMINEDISCOUNT").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("CASE_ID").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PRIORITY").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("REGISTEROR").ToString() & "' ," & _
                          " '" & MsDateTime(DS.Tables(0).Rows(0).Item("UPLOAD_DATE").ToString(), DS.Tables(0).Rows(0).Item("request_time").ToString()) & "' ," & _
                          " '" & MsDateTime(DS.Tables(0).Rows(0).Item("START_REGISTERDATE").ToString(), DS.Tables(0).Rows(0).Item("request_time").ToString()) & "' ," & _
                          " '" & MsDateTime(DS.Tables(0).Rows(0).Item("REGISTERDATE").ToString(), DS.Tables(0).Rows(0).Item("request_time").ToString()) & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("FILM1417").ToString() & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("START_EXAMINEDATE").ToString()) & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("EXAMINEDATE").ToString()) & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("DIAGNOSISRESULT").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("CLINIC_ACCORD").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("REPORTDOCTOR").ToString() & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("START_REPORTDATE").ToString()) & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("REPORTDATE").ToString()) & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("START_AUDITINGDATE").ToString()) & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("AUDITINGDATE").ToString()) & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("AUDITINGDATE1").ToString()) & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("PROF").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("HOSPITAL_ID").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("BSTATE").ToString() & "' ," & _
                          " '" & MsDateTime(DS.Tables(0).Rows(0).Item("LASTUPDATEDATE").ToString(), DS.Tables(0).Rows(0).Item("request_time").ToString()) & "' ," & _
                          " '" & MsDate(DS.Tables(0).Rows(0).Item("LASTPRINTDATE").ToString()) & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("STUDIESMEMO").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("REQUESTINGDEPARTMENT").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("REFERINGPHYSICIAN").ToString() & "' ," & _
                          " '" & DS.Tables(0).Rows(0).Item("NATION").ToString() & "' "

                If chkEx2GDT.Checked Then
                    'Dim FILE_NAME As String = ClsSQLhelper.ImportPath + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString() + ".GDT"
                    'Dim FILE_NAME As String = "D:\DD\" + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString() + ".GDT"
                    'Dim FILE_NAME As String = "X:\" + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString() + ".txt"
                    Dim FILE_NAME As String = ClsSQLhelper.ExportPath + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString() + ".txt"
                    Dim i As Integer
                    Dim aryText(28) As String

                    'aryText(0) = "01380006302"
                    'aryText(1) = "014810000231"
                    'aryText(2) = "014921802.00"
                    'aryText(3) = "0193000" + DS.Tables(0).Rows(0).Item("PATIENT_ID").ToString()
                    'aryText(4) = "0143101" + HL7ReserverClear(DS.Tables(0).Rows(0).Item("fname").ToString())
                    'aryText(5) = "0153102" + HL7ReserverClear(DS.Tables(0).Rows(0).Item("lname").ToString())
                    'aryText(6) = "0173103" + iQDate(DS.Tables(0).Rows(0).Item("BIRTHDATE").ToString())
                    'aryText(7) = "0193110" + DS.Tables(0).Rows(0).Item("sex").ToString()
                    'aryText(8) = "0198402" + ModalityGroup(DS.Tables(0).Rows(0).Item("xray_items_group").ToString())
                    'aryText(9) = "0198606" + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString()
                    'aryText(10) = "0298411" + DS.Tables(0).Rows(0).Item("EXAMINEPART").ToString()
                    'aryText(11) = "0176200" + iQDate(DS.Tables(0).Rows(0).Item("EXAMINEDATE").ToString())
                    'aryText(12) = "0566227" + DS.Tables(0).Rows(0).Item("PATIENTTYPE").ToString()

                    aryText(0) = "[RecordNum1]"
                    'แปลงชื่อไทย To Eng 
                    If chkiQTH2EN.Checked Then
                        If (ModalityGroup(DS.Tables(0).Rows(0).Item("xray_items_group").ToString()) = "CT") Or (ModalityGroup(DS.Tables(0).Rows(0).Item("xray_items_group").ToString()) = "US") Then

                            Dim Pname As String
                            If Trim(DS.Tables(0).Rows(0).Item("pname").ToString()) = "นาย" Then
                                Pname = "MR."
                            ElseIf Trim(DS.Tables(0).Rows(0).Item("pname").ToString()) = "นาง" Then
                                Pname = "MRS."
                            ElseIf Trim(DS.Tables(0).Rows(0).Item("pname").ToString()) = "น.ส." Then
                                Pname = "MISS."
                            ElseIf Trim(DS.Tables(0).Rows(0).Item("pname").ToString()) = "นางสาว" Then
                                Pname = "MISS"
                            ElseIf Trim(DS.Tables(0).Rows(0).Item("pname").ToString()) = "" Then
                                Pname = ""
                            Else
                                Pname = ""
                            End If

                            aryText(1) = "Patient Name = " + UCase(Pname & TH2R(DS.Tables(0).Rows(0).Item("fname").ToString()) & "^" & TH2R(DS.Tables(0).Rows(0).Item("lname").ToString()))

                        Else
                            aryText(1) = "Patient Name = " + HL7ReserverClear(trans_fname)
                        End If
                    Else
                        aryText(1) = "Patient Name = " + HL7ReserverClear(trans_fname)
                    End If
                    '-------------Check CT Map 0206674.HN ---------------
                    If (ModalityGroup(DS.Tables(0).Rows(0).Item("xray_items_group").ToString()) = "CT") Or (ModalityGroup(DS.Tables(0).Rows(0).Item("xray_items_group").ToString()) = "US") Then
                        aryText(2) = "Patient ID = " + DS.Tables(0).Rows(0).Item("PATIENT_ID").ToString() & ".HN"
                    Else
                        aryText(2) = "Patient ID = " + DS.Tables(0).Rows(0).Item("PATIENT_ID").ToString()
                    End If

                    aryText(3) = "Date of Birth = " + iQDate(DS.Tables(0).Rows(0).Item("BIRTHDATE").ToString())
                    aryText(4) = "Sex = " + DS.Tables(0).Rows(0).Item("PATIENTSEX").ToString()
                    aryText(5) = "Accession Number = " + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString()
                    aryText(6) = "Referring Physician = "
                    aryText(7) = "Requesting Physician = " + DS.Tables(0).Rows(0).Item("REFERINGPHYSICIAN").ToString()
                    aryText(8) = "Requested Procedure ID = " + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString()

                    aryText(10) = "Requested Procedure Priority = "
                    aryText(11) = "Scheduled AE Station = " + DS.Tables(0).Rows(0).Item("PATIENTTYPE").ToString()
                    aryText(12) = "Modality = " + ModalityGroup(DS.Tables(0).Rows(0).Item("xray_items_group").ToString())
                    aryText(13) = "Scheduled Start Date = " + iQDate(DS.Tables(0).Rows(0).Item("EXAMINEDATE").ToString())
                    aryText(14) = "Scheduled Start Time = " + DS.Tables(0).Rows(0).Item("request_time").ToString()
                    aryText(15) = "Performing Physician = "
                    aryText(16) = "Scheduled Procedure ID = "
                    aryText(17) = "Scheduled Procedure Description = "
                    aryText(18) = "Scheduled Procedure Location = "
                    aryText(19) = "Scheduled Procedure Pre-Medication = "
                    aryText(20) = "Scheduled Procedure Contrast Agent = "
                    'aryText(21) = "Procedure Code Value =  " + DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString()
                    aryText(21) = "Procedure Code Value =  " + DS.Tables(0).Rows(0).Item("XRAYITEMCODE").ToString()
                    aryText(22) = "Procedure Code Meaning = " + HL7ReserverClear(DS.Tables(0).Rows(0).Item("xray_items_name").ToString())
                    aryText(23) = "Procedure Code Scheme = " + DS.Tables(0).Rows(0).Item("PATIENTTYPE").ToString()
                    aryText(24) = "Procedure Code Scheme Version = "

                    'Check ว่่าให้ส่งเป็น Mapping Code หรือไม่ 
                    If chkMappingType.Checked Then
                        iQstrSql1 = "select *  " & _
                                    " from mappingcode " & _
                                    " where xray_item_code  ='" & DS.Tables(0).Rows(0).Item("xray_items_code").ToString() & "' "

                        iQDS1 = iQMysql.iQGetMYSQLDataset(iQstrSql1, "DS_mappingcode", "mappingcode")
                        If iQDS1.Tables(0).Rows.Count > 0 Then
                            aryText(9) = "Requested Procedure Description = " + HL7ReserverClear(iQDS1.Tables(0).Rows(0).Item("remark").ToString())
                            aryText(25) = "Protocol Code Value = " + iQDS1.Tables(0).Rows(0).Item("ris_code").ToString()
                            aryText(26) = "Protocol Code Meaning = " + HL7ReserverClear(iQDS1.Tables(0).Rows(0).Item("exam_tag").ToString())
                            If iQDS1.Tables(0).Rows(0).Item("group2").ToString() = "" Then
                                aryText(27) = "Protocol Code Scheme = " + HL7ReserverClear(DS.Tables(0).Rows(0).Item("xray_items_name").ToString())
                            Else
                                aryText(27) = "Protocol Code Scheme = " + HL7ReserverClear(iQDS1.Tables(0).Rows(0).Item("group2").ToString())
                            End If
                            aryText(28) = "Protocol Code Scheme Version = "

                        Else
                            aryText(25) = "Protocol Code Value = " + DS.Tables(0).Rows(0).Item("XRAYITEMCODE").ToString()
                            aryText(26) = "Protocol Code Meaning = " + HL7ReserverClear(DS.Tables(0).Rows(0).Item("xray_items_name").ToString())
                            aryText(27) = "Protocol Code Scheme = " + HL7ReserverClear(DS.Tables(0).Rows(0).Item("xray_items_name").ToString())
                            aryText(28) = "Protocol Code Scheme Version = "

                        End If
                    Else
                        aryText(9) = "Requested Procedure Description = "
                        aryText(25) = "Protocol Code Value = " + DS.Tables(0).Rows(0).Item("XRAYITEMCODE").ToString()
                        aryText(26) = "Protocol Code Meaning = " + HL7ReserverClear(DS.Tables(0).Rows(0).Item("xray_items_name").ToString())
                        aryText(27) = "Protocol Code Scheme = " + HL7ReserverClear(DS.Tables(0).Rows(0).Item("xray_items_name").ToString())
                        aryText(28) = "Protocol Code Scheme Version = "

                    End If
                    ' Check Export to UTF8
                    If chkGDTExport2UTF.Checked Then
                        'UTF8
                        Dim objWriter As New System.IO.StreamWriter(FILE_NAME)
                        For i = 0 To 28
                            objWriter.WriteLine(aryText(i))
                        Next
                        objWriter.Close()
                    Else
                        'ANSI (Thai)
                        Dim objWriter As New System.IO.StreamWriter(FILE_NAME, False, System.Text.Encoding.GetEncoding(874))
                        For i = 0 To 28
                            objWriter.WriteLine(aryText(i))
                        Next

                        objWriter.Close()
                    End If



                   

                End If
            End If
        Catch ex As Exception
            Me.txtConsole.Text = Err.Description & vbNewLine & Me.txtConsole.Text
            MsgBox(ex.Message.ToString)
        End Try

    End Function
    Public Sub GetData()
        Try
            Dim getdate As String = MsDate(Date.Now)
            Dim col As String

            col = "  ACCESSIONNUMBER ,MODALITY,PATIENT_ID,PATIENTTYPE,CLINICAL_ID,FIRSTNAME,LASTNAME,PATIENTSEX, " & _
                          " PATIENTAGE,BIRTHDATE,BEDID,PATIENTSIZE,PATIENTWEIGHT,PATIENTTELEPHONENUMBER,ADMITTINGDATE, " & _
                          " REQUESTEDPROCEDUREDESCRIPTION,EXAMINEPART,PART_COUNT,EXAMINEFEE,EXAMINEDISCOUNT,CASE_ID,PRIORITY, " & _
                          " REGISTEROR,UPLOAD_DATE,START_REGISTERDATE,REGISTERDATE,FILM1417,START_EXAMINEDATE,EXAMINEDATE, " & _
                          " DIAGNOSISRESULT,CLINIC_ACCORD,REPORTDOCTOR,START_REPORTDATE,REPORTDATE,START_AUDITINGDATE,AUDITINGDATE, " & _
                          " AUDITINGDATE1,PROF,HOSPITAL_ID,BSTATE,LASTUPDATEDATE,LASTPRINTDATE,STUDIESMEMO,REQUESTINGDEPARTMENT, " & _
                          " REFERRINGPHYSICIAN,NATION "
            '------ChkXrayRecive  Checked----
            If chkXrayRecive.Checked Then
                '*** DataTable ***' 

                Strsql = " select r.xn,r.hn,concat(p.pname,p.fname,'-',p.lname)as ptname,h.receive_no, " & _
                         " (select xray_items_name from xray_items where xray_items_code = r.xray_items_code )as xray_name " & _
                         " from xray_report r " & _
                         " left outer join xray_head h on h.vn =r.vn " & _
                         " left outer join patient p on p.hn =r.hn " & _
                         " where   r.request_date ='" & getdate & "'  " & _
                         " and h.receive_no is not null "


                '*** DataTable ***'  
                Dim DS1 As New DataSet

                DS1 = Mysql1.GetMYSQLDataset(Strsql, "ds_xray_report", "xray_report")

                If DS1.Tables(0).Rows.Count > 0 Then
                    For i = 0 To DS1.Tables(0).Rows.Count - 1

                        Dim DS2 As New DataSet

                        StrMssql = "select xn from xn2acc where xn='" & DS1.Tables(0).Rows(i).Item("xn").ToString() & "' "

                        DS2 = Mssql1.GetMSSQLDataset(StrMssql, "DS_xn2acc", "xn2acc")

                        If DS2.Tables(0).Rows.Count = 0 Then

                            StrMssql = "INSERT INTO RIS_STUDIES (" & col & ")" & _
                                       "  VALUES(" & xhosData(DS2.Tables(0).Rows(0).Item("xn").ToString()) & ")"
                            Mssql1.MsExecute(StrMssql)

                            '---UP xray_PACK 
                            UpXray_pacs(DS2.Tables(0).Rows(0).Item("xn").ToString())
                            '---------------

                            Me.txtConsole.Text = "XN= " & DS1.Tables(0).Rows(i).Item("xn").ToString() & "  HN= " & DS1.Tables(0).Rows(i).Item("hn").ToString() & " PT=  " & DS1.Tables(0).Rows(i).Item("ptname").ToString() & " Exam.=  " & DS1.Tables(0).Rows(i).Item("xray_name").ToString() & vbNewLine & Me.txtConsole.Text
                            '--------------
                            DaySrearch()
                        End If

                    Next i

                Else
                    Exit Sub

                End If

            Else
                '------ChkXrayRecive NOT Checked----
                Dim PacsXn As String = GetLastXN()
                Dim HosXn As String = GetHosXN()
                If Int(PacsXn) < Int(HosXn) Then

                    '--------Check ข้อมูลในวัน----
                    If chkLastXN.Checked Then


                        Strsql = " select r.xn,r.hn,concat(p.pname,p.fname,'-',p.lname)as ptname," & _
                                " (select xray_items_name from xray_items where xray_items_code = r.xray_items_code )as xray_name " & _
                                " from xray_report  r,patient p where r.hn =p.hn and r.xn > '" & GetLastXN() & "' AND r.request_date ='" & getdate & "' "

                    Else
                        Strsql = " select r.xn,r.hn,concat(p.pname,p.fname,'-',p.lname)as ptname," & _
                                 " (select xray_items_name from xray_items where xray_items_code = r.xray_items_code )as xray_name " & _
                                 " from xray_report  r,patient p where r.hn =p.hn and r.xn > '" & GetLastXN() & "'  "

                    End If


                    '*** DataTable ***' 
                    Dim DS As New DataSet

                    DS = Mysql1.GetMYSQLDataset(Strsql, "ds_xray_report", "xray_report")

                    If DS.Tables(0).Rows.Count > 0 Then
                        For i = 0 To DS.Tables(0).Rows.Count - 1


                            StrMssql = "INSERT INTO RIS_STUDIES (" & col & ")" & _
                                       "  VALUES(" & xhosData(DS.Tables(0).Rows(i).Item("xn").ToString()) & ")"
                            'MsgBox(StrMssql, vbCritical)
                            Mssql1.MsExecute(StrMssql)

                            '---UP xray_PACK 
                            UpXray_pacs(DS.Tables(0).Rows(i).Item("xn").ToString())
                            '---------------

                            Me.txtConsole.Text = "XN= " & DS.Tables(0).Rows(i).Item("xn").ToString() & "  HN= " & DS.Tables(0).Rows(i).Item("hn").ToString() & " PT=  " & DS.Tables(0).Rows(i).Item("ptname").ToString() & " Exam.=  " & DS.Tables(0).Rows(i).Item("xray_name").ToString() & vbNewLine & Me.txtConsole.Text
                            '--------------
                        Next i
                        DaySrearch()
                    Else
                        Exit Sub

                    End If


                Else
                    Exit Sub
                End If
                '------end -----PacsXn < HosXn---
            End If
        Catch ex As Exception
            Me.txtConsole.Text = Err.Description & vbNewLine & Me.txtConsole.Text
            MsgBox(ex.Message.ToString)
        End Try

    End Sub
    Private Function UpXray_pacs(ByVal xn As String) As String
        Try
            Dim getxn As String
            Dim DS1 As New DataSet
            Dim DS2 As New DataSet
            Dim DS3 As New DataSet
            Dim strSql1 As String
            Dim strSql2 As String
            Dim strSql3 As String

            '-------UP xray_pacs---------
            strSql1 = "select  *   from xray_pack  where xray_report_xn = '" & xn & "'  "
            DS1 = Mysql1.GetMYSQLDataset(strSql1, "ds_xray_pack", "xray_pack")

            If DS1.Tables(0).Rows.Count > 0 Then
                '-----------
            Else
                strSql1 = "insert xray_pack (xray_report_xn, pack_accession_no, xray_pack_status, hos_guid) " & vbNewLine _
                    & "values (" & Str(xn) & ", '" & Trim(Str(xn)) & "','N','" & uuid() & "' ) "
                Mysql1.MySQLExecute(strSql1)
            End If

            '-------UP xn2acc---------
            StrMssql = "select * from xn2acc where xn = '" & xn & "'"
            DS2 = Mssql1.GetMSSQLDataset(StrMssql, "DS_xn2acc", "xn2acc")

            If DS2.Tables(0).Rows.Count > 0 Then
                '---------
            Else
                strSql2 = "insert into xn2acc (xn,ACCESSIONNUMBER) values(" & Str(xn) & ", '" & Trim(Str(xn)) & "') "
                Mssql1.MsExecute(strSql2)
            End If
            '--------------------------
        Catch ex As Exception
            Me.txtConsole.Text = Err.Description & vbNewLine & Me.txtConsole.Text
            MsgBox(ex.Message.ToString)
        End Try
    End Function

    Private Sub UpPacsReport2HOS()
        Dim DS1 As New DataSet
        Dim DS2 As New DataSet
        Dim DS4 As New DataSet
        Dim strSql1 As String
        Dim strSql2 As String
        Dim strSql3 As String
        Dim strSql4 As String
        Dim strSql5 As String
        Dim strSql6 As String
        Dim strSql7 As String
        Dim iQDS1 As New DataSet
        Dim iQstrSql1 As String

        Dim getdate As String = MsDate(Date.Now)

        '------Check Report----------
        '--Check result Diag Pacs to hos --------------------
        ' If Check result Image for to day
        If chkConfirm.Checked Then

            If chkiQPACs.Checked Then
                'select * from worklist where status ='200' and createdate ='2013-04-23'

                iQstrSql1 = "select accessionnum " & _
                  " from worklist " & _
                  " where (studyuid is not null) and (status = 900) and date(received)  ='" & getdate & "' "

                iQDS1 = iQMysql.iQGetMYSQLDataset(iQstrSql1, "DS_worklist", "worklist")
                If iQDS1.Tables(0).Rows.Count > 0 Then
                    For j = 0 To iQDS1.Tables(0).Rows.Count - 1
                        '-------------
                        strSql1 = "select  xray_pack_status  from xray_pack  where xray_report_xn = '" & Trim(iQDS1.Tables(0).Rows(j).Item("accessionnum").ToString()) & "' and  xray_pack_status = 'N'  "
                        DS1 = Mysql1.GetMYSQLDataset(strSql1, "ds_xray_pack", "xray_pack")

                        If DS1.Tables(0).Rows.Count > 0 Then
                            If DS1.Tables(0).Rows(0).Item("xray_pack_status").ToString <> "Y" Then
                                strSql5 = "update xray_report " & _
                                    " set confirm = 'Y' " & _
                                    " where xn = '" & Trim(iQDS1.Tables(0).Rows(j).Item("accessionnum").ToString()) & "' "
                                Mysql1.MySQLExecute(strSql5)

                                strSql6 = "update xray_pack  " & _
                                    " set xray_pack_status = 'Y' " & _
                                    " where xray_report_xn = '" & Trim(iQDS1.Tables(0).Rows(j).Item("accessionnum").ToString()) & "' "
                                Mysql1.MySQLExecute(strSql6)

                                strSql7 = "UPDATE xn2acc  " & _
                                     " SET Exposed = 'True' " & _
                                     " where xn = '" & Trim(iQDS1.Tables(0).Rows(j).Item("accessionnum").ToString()) & "' "
                                Mssql1.MsExecute(strSql7)

                                '------------
                                Me.txtConsole.Text = "XN= " & Trim(iQDS1.Tables(0).Rows(j).Item("accessionnum").ToString()) & "  Confirm Image" & vbNewLine & Me.txtConsole.Text

                                '-------------

                            End If

                        End If


                    Next j

                End If
            Else
                strSql4 = "SELECT ACCESSIONNUMBER " & _
                   " FROM RIS_STUDIES  " & _
                   " WHERE(STUDYUID IS NOT NULL) AND (BSTATE = 4) AND (EXAMINEDATE ='" & getdate & "') "

                DS4 = Mssql1.GetMSSQLDataset(strSql4, "DS_RIS_STDIES", "RIS_STDIES")
                If DS4.Tables(0).Rows.Count > 0 Then
                    For i = 0 To DS4.Tables(0).Rows.Count - 1
                        '-------------
                        strSql1 = "select  xray_pack_status  from xray_pack  where xray_report_xn = '" & Trim(DS4.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "' and  xray_pack_status = 'N'  "
                        DS1 = Mysql1.GetMYSQLDataset(strSql1, "ds_xray_pack", "xray_pack")

                        If DS1.Tables(0).Rows.Count > 0 Then
                            If DS1.Tables(0).Rows(0).Item("xray_pack_status").ToString <> "Y" Then
                                strSql5 = "update xray_report " & _
                                    " set confirm = 'Y' " & _
                                    " where xn = '" & Trim(DS4.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "' "
                                Mysql1.MySQLExecute(strSql5)

                                strSql6 = "update xray_pack  " & _
                                    " set xray_pack_status = 'Y' " & _
                                    " where xray_report_xn = '" & Trim(DS4.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "' "
                                Mysql1.MySQLExecute(strSql6)

                                strSql7 = "UPDATE xn2acc  " & _
                                     " SET Exposed = 'True' " & _
                                     " where xn = '" & Trim(DS4.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "' "
                                Mssql1.MsExecute(strSql7)

                                '------------
                                Me.txtConsole.Text = "XN= " & Trim(DS4.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "  Confirm Image" & vbNewLine & Me.txtConsole.Text

                                '-------------

                            End If

                        End If

                    Next i
                End If

            End If 'check iQPACS

        End If   'check Image

        '-------Check Diag Report ------
        If chkDiagReport.Checked Then

            strSql2 = "SELECT ACCESSIONNUMBER, PATIENT_ID, EXAMINEDESCRIPTION, DIAGNOSISPROMPT " & _
            " FROM RIS_STUDIES  " & _
            " WHERE((EXAMINEDESCRIPTION IS NOT NULL) OR (DIAGNOSISPROMPT IS NOT NULL)) AND (EXAMINEDATE ='" & getdate & "') "
            DS2 = Mssql1.GetMSSQLDataset(strSql2, "DS_RIS_STDIES", "RIS_STDIES")

            If DS2.Tables(0).Rows.Count > 0 Then

                For i = 0 To DS2.Tables(0).Rows.Count - 1
                    '-------------
                    strSql1 = "select  report_text  from xray_report  " & _
                              " where xn =  '" & Trim(DS2.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "' " & _
                              " and  (report_text is null or report_text ='')  "

                    DS1 = Mysql1.GetMYSQLDataset(strSql1, "ds_xray_report", "xray_report")

                    If DS1.Tables(0).Rows.Count > 0 Then
                        'If IsDBNull(DS1.Tables(0).Rows(0).Item("report_text")) Then
                        Dim Report_Desc As String = Trim(DS2.Tables(0).Rows(i).Item("EXAMINEDESCRIPTION").ToString()) & " DIAG : " & Trim(DS2.Tables(0).Rows(i).Item("DIAGNOSISPROMPT").ToString())
                        'strSql3 = "update xray_report " & _
                        '          " set report_text = '" & Trim(DS2.Tables(0).Rows(i).Item("EXAMINEDESCRIPTION").ToString()) & " DIAG : " & Trim(DS2.Tables(0).Rows(i).Item("DIAGNOSISPROMPT").ToString()) & "' " & _
                        '          " where xn = '" & Trim(DS2.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "' "
                        strSql3 = "update xray_report " & _
                                  " set report_text = '" & Report_Desc & "', report_rtf = '" & Report_Desc & "',confirm_read_film ='Y' " & _
                                  " where xn = '" & Trim(DS2.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString()) & "' "
                        Mysql1.MySQLExecute(strSql3)
                        '------------
                        Me.txtConsole.Text = "XN= " & DS2.Tables(0).Rows(i).Item("ACCESSIONNUMBER").ToString() & " HN= " & DS2.Tables(0).Rows(i).Item("PATIENT_ID").ToString() & " Update Diag Report to HIS " & vbNewLine & Me.txtConsole.Text
                        '-------------
                        'End If
                    End If
                Next i

            End If

        End If          '-------Check Diag Report ------
    End Sub

    Private Sub frmBroker_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

        Me.cmdStart.Enabled = True
        Me.cmdStop.Enabled = False
        

        If chkLastXN.Checked Then
            ClsSQLhelper.chkLastXN = "Y"
        Else
            ClsSQLhelper.chkLastXN = "N"
        End If

        If chkConfirm.Checked Then
            ClsSQLhelper.chkConfirm = "Y"
        Else
            ClsSQLhelper.chkConfirm = "N"
        End If

        If (chkDiagReport.Checked) Then
            ClsSQLhelper.chkDiagReport = "Y"
        Else
            ClsSQLhelper.chkDiagReport = "N"
        End If

        If (chkEncode.Checked) Then
            ClsSQLhelper.chkEncode = "Y"
        Else
            ClsSQLhelper.chkEncode = "N"
        End If

        If (chkThaiToEnglish.Checked) Then
            ClsSQLhelper.chkThaiToEnglish = "Y"
        Else
            ClsSQLhelper.chkThaiToEnglish = "N"
        End If

        If (chkXrayRecive.Checked) Then
            ClsSQLhelper.chkXrayRecive = "Y"
        Else
            ClsSQLhelper.chkXrayRecive = "N"
        End If

        If (chkEx2GDT.Checked) Then

            ClsSQLhelper.chkEx2GDT = "Y"
        Else
            ClsSQLhelper.chkEx2GDT = "N"
        End If

        If (chkiQPACs.Checked) Then

            ClsSQLhelper.chkiQPACs = "Y"
        Else
            ClsSQLhelper.chkiQPACs = "N"
        End If

        If (chkiQTH2EN.Checked) Then

            ClsSQLhelper.chkiQTH2EN = "Y"
        Else
            ClsSQLhelper.chkiQTH2EN = "N"
        End If

        If (chkMappingType.Checked) Then

            ClsSQLhelper.chkMappingType = "Y"
        Else
            ClsSQLhelper.chkMappingType = "N"
        End If

        If (chkGDTExport2UTF.Checked) Then

            ClsSQLhelper.chkGDTExport2UTF = "Y"
        Else
            ClsSQLhelper.chkGDTExport2UTF = "N"
        End If



        If txtInterval.Text <> "" Then
            ClsSQLhelper.SecInterval = txtInterval.Text
        Else
            ClsSQLhelper.SecInterval = "3"
        End If


        frmMain.Show()
        Me.Hide()


    End Sub


    Private Sub frmBroker_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        '*** DataTable ***' 
        Dim DS As New DataSet

        Dim StrMssql As String
        '------------------
        Dim LicenseCode As String
        Dim key As String
        Dim hcode As String
        Dim UserMode As String


        Me.Text = ver

        Me.cmdStart.Enabled = True
        Me.cmdStop.Enabled = False

        Dim getdate As String = MsDate(Date.Now)
        '--------------
        'LicenseCode = ClsSQLhelper.License

        'If LicenseCode = "" Then
        '    MsgBox("ตรวจพบการตั้งค่าคีย์ไม่ถูกต้อง กรุณาติดต่อ Admin", MsgBoxStyle.Information)
        'Else
        '    key = "sys11266"
        '    hcode = Crypto.Decrypt(ClsSQLhelper.License, key)
        '    ' txtGenCode.Text = LicenseCode

        '    Dim DS1 As New DataSet
        '    Dim strSql1 As String
        '    Dim Mysql1 As New ClsSQLhelper
        '    '------------------------
        '    strSql1 = "select hospitalcode from opdconfig  "
        '    DS1 = Mysql1.GetMYSQLDataset(strSql1, "ds_hosname", "opdconfig")

        '    If DS1.Tables(0).Rows.Count > 0 Then
        '        If DS1.Tables(0).Rows(0).Item("hospitalcode").ToString() = "" Then
        '            MsgBox("ตรวจพบการตั้งค่าไม่ถูกต้อง กรุณาติดต่อ Admin", MsgBoxStyle.Information)
        '            'End

        '        Else

        '            If DS1.Tables(0).Rows(0).Item("hospitalcode").ToString() <> hcode Then
        '                'MsgBox("ตรวจพบการตั้งค่าคีย์ไม่ถูกต้อง กรุณาติดต่อ Admin", MsgBoxStyle.Information)
        '                frmLicense.Show()

        '            End If
        '        End If

        '    End If
        '    '------------------------

        'End If


        '----Check Config----------
        If (ClsSQLhelper.chkLastXN) = "Y" Then
            chkLastXN.Checked = True
        Else
            chkLastXN.Checked = False
        End If

        If (ClsSQLhelper.chkConfirm) = "Y" Then
            chkConfirm.Checked = True
        Else
            chkConfirm.Checked = False
        End If

        If (ClsSQLhelper.chkDiagReport) = "Y" Then
            chkDiagReport.Checked = True
        Else
            chkDiagReport.Checked = False
        End If

        If (ClsSQLhelper.chkEncode) = "Y" Then
            chkEncode.Checked = True
        Else
            chkEncode.Checked = False
        End If

        If (ClsSQLhelper.chkThaiToEnglish) = "Y" Then
            chkThaiToEnglish.Checked = True
        Else
            chkThaiToEnglish.Checked = False
        End If


        If (ClsSQLhelper.chkXrayRecive) = "Y" Then
            chkXrayRecive.Checked = True
        Else
            chkXrayRecive.Checked = False
        End If

        If (ClsSQLhelper.chkEx2GDT) = "Y" Then
            chkEx2GDT.Checked = True
        Else
            chkEx2GDT.Checked = False
        End If

        If (ClsSQLhelper.chkiQPACs) = "Y" Then
            chkiQPACs.Checked = True
        Else
            chkiQPACs.Checked = False
        End If

        If (ClsSQLhelper.chkiQTH2EN) = "Y" Then
            chkiQTH2EN.Checked = True
        Else
            chkiQTH2EN.Checked = False
        End If

        If (ClsSQLhelper.chkMappingType) = "Y" Then
            chkMappingType.Checked = True
        Else
            chkMappingType.Checked = False
        End If

        If (ClsSQLhelper.chkGDTExport2UTF) = "Y" Then
            chkGDTExport2UTF.Checked = True
        Else
            chkGDTExport2UTF.Checked = False
        End If


        If ClsSQLhelper.SecInterval <> "" Then
            txtInterval.Text = ClsSQLhelper.SecInterval
        Else
            txtInterval.Text = "3"
        End If




        '----------------------------

        If ClsSQLhelper.MyTestConnectionResult = True Then
            If ClsSQLhelper.MsTestConnectionResult = True Then

                Strsql = "SELECT " & _
                  " ACCESSIONNUMBER , " & _
                  " PATIENT_ID, " & _
                  " PATIENTTYPE," & _
                  " FIRSTNAME," & _
                  " LASTNAME," & _
                  " PATIENTAGE," & _
                  " EXAMINEPART, " & _
                  " START_REGISTERDATE, " & _
                  " CASE WHEN isnumeric(ACCESSIONNUMBER) = 1 THEN CONVERT(float,ACCESSIONNUMBER) END AS ACCNO " & _
                  " FROM RIS_STUDIES  " & _
                  " WHERE EXAMINEDATE ='" & getdate & "' ORDER BY ACCNO DESC "

                DS = Mssql1.GetMSSQLDataset(Strsql, "DS_RIS_STUDIES", "RIS_STUDIES")
                DataGridWorklist.DataSource = DS.Tables(0)
                With DataGridWorklist
                    .Columns.Item(0).HeaderText = "Order No."
                    .Columns.Item(0).Width = "60"
                    .Columns.Item(1).HeaderText = "HN"
                    .Columns.Item(1).Width = "60"
                    .Columns.Item(2).HeaderText = "PT_TYPE"
                    .Columns.Item(2).Width = "60"
                    .Columns.Item(3).HeaderText = "ชื่อ-สกุล"
                    .Columns.Item(3).Width = "150"
                    .Columns.Item(4).HeaderText = "ชื่อ-สกุล (EN)"
                    .Columns.Item(4).Width = "150"
                    .Columns.Item(5).HeaderText = "อายุ"
                    .Columns.Item(5).Width = "50"
                    .Columns.Item(6).HeaderText = "รายการ"
                    .Columns.Item(6).Width = "100"
                    .Columns.Item(7).HeaderText = "วันที่สั่ง"
                    .Columns.Item(7).Width = "100"

                End With
            Else
                MsgBox("ไม่สามารถติดต่อฐานข้อมูลMSSQLได้กรุณาตรวจสอบการตั้งค่าการเชื่อมต่อ", vbCritical)
            End If

        Else

            MsgBox("ไม่สามารถติดต่อฐานข้อมูลMySQLได้กรุณาตรวจสอบการตั้งค่าการเชื่อมต่อ", vbCritical)

            'frmMySQLSetting.Show()

        End If

        ''------------------------
        'strSql1 = "select hospitalcode from opdconfig  "
        'DS1 = Mysql1.GetMYSQLDataset(strSql1, "ds_hosname", "opdconfig")

        'If DS1.Tables(0).Rows.Count > 0 Then
        '    If DS1.Tables(0).Rows(0).Item("hospitalcode").ToString() = "" Then
        '        MsgBox("ตรวจพบการตั้งค่าไม่ถูกต้อง", MsgBoxStyle.Information)
        '        End
        '    End If
        'Else
        '    frmMySQLSetting.Show()
        'End If

        '------------------------
        '*** DataTable ***' 

        'StrMssql = "SELECT MAX(ACCESSIONNUMBER)as MAX_ORDER FROM RIS_STUDIES  "

        'DS2 = Mssql1.GetMSSQLDataset(StrMssql, "DS_RIS_STUDIES", "RIS_STUDIES")
        ''DataGridViewPACs.DataSource = DS.Tables(0)
        'If DS2.Tables(0).Rows.Count > 0 Then
        '    txtLastXN.Text = DS2.Tables(0).Rows(0).Item(0).ToString
        'Else
        '    frmMsSQLSetting.Show()
        'End If





        'Strsql = "select * from sys_var  where sys_name like 'PAC%'"
        'Strsql = "select" & _
        '          " x.XN , " & _
        '          " x.hn as PATIENT_ID, " & _
        '          "  if(x.an<>'' ,'IPD',h.department) as PATIENT_TYPE," & _
        '          "  concat(p.pname,p.fname,'-',p.lname)   as PATIENT_NAME," & _
        '          "  timestampdiff(year,p.birthday,now())  as PATIENT_AGE," & _
        '          "  x.request_date as Order_Date , " & _
        '          "  x.request_time as Order_Time " & _
        '          "  from xray_report x " & _
        '          "  left join xray_room r  on x.xray_room_id = r.xray_room" & _
        '          "  left join patient p  on x.hn = p.hn" & _
        '          "  left join xray_items i   on x.xray_items_code = i.xray_items_code" & _
        '          "  left join xray_head h  on x.vn = h.vn" & _
        '          "  left join doctor d    on x.request_doctor = d.code" & _
        '          "  left join doctor d2  on x.report_doctor = d2.code" & _
        '          "  left join kskdepartment k  on  k.depcode = x.request_depcode" & _
        '          "  left join nationality n on n.nationality = p.nationality " & _
        '          "  where request_date = '" & getdate & "' order by xn desc "

        'DS = Mysql1.GetMYSQLDataset(Strsql, "DS_onlineuser", "sys_var")

        Me.MdiParent = frmMain

    End Sub

    Private Sub CmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdSearch.Click
        'Dim dgvRow As DataGridViewRow = Nothing

        'For Each dgvRow In DataGridWorklist.SelectedRows
        '    dgvRow.Selected = txtSearch.Text
        '    Exit For
        'Next

        With DataGridWorklist
            Dim i, j As Integer
            'Dim aa As String
            If txtSearch.Text = "" Then
                MsgBox("กรุณากรอกข้อมูลที่ต้องการค้นหา", , "ข้อมูลไม่ถูกต้อง")
                Exit Sub
            End If




            'For i = 0 To .RowCount - i
            '    Select Case ComboSel.Text
            '        Case "HN"
            '            'aa = .Rows.Item(i).Cells(1).Value

            '            If .Rows.Item(i).Cells(1).Value.ToString = txtSearch.Text Then
            '                .ClearSelection()
            '                .Rows(i).Selected = True
            '                .Refresh()

            '                Exit Sub
            '            End If
            '        Case "XN"
            '            If .Rows.Item(i).Cells(0).Value.ToString = txtSearch.Text Then
            '                .ClearSelection()
            '                .Rows(i).Selected = True
            '                .Refresh()

            '                Exit Sub
            '            End If

            '        Case "ชื่อผู้ป่วย"
            '            If .Rows.Item(i).Cells(3).Value.ToString = txtSearch.Text Then
            '                .ClearSelection()
            '                .Rows(i).Selected = True
            '                .Refresh()
            '                Exit Sub
            '            End If

            '    End Select
            'Next i

        End With
    End Sub

    Private Sub DataGridWorklist_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs)
        If e.Button = Windows.Forms.MouseButtons.Right Then

            Dim hit As DataGridView.HitTestInfo = DataGridWorklist.HitTest(e.X, e.Y)

            DataGridWorklist.Rows(hit.RowIndex).Selected = True


            'DataGridWorklist.ContextMenu.Show(Me.DataGridWorklist, New Point(hit.RowIndex, hit.ColumnIndex))
            'DataGridWorklist.ContextMenuStrip.Show(Me.DataGridWorklist, New Point(hit.RowIndex, hit.ColumnIndex))

        End If
    End Sub

    Private Sub cmdDaySrearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDaySrearch.Click

        DaySrearch()

    End Sub

    Private Sub DaySrearch()
        Dim DS As New DataSet
        Dim getdate As String = MsDate(DateTimePicker1.Value)

        Strsql = "SELECT " & _
                   " ACCESSIONNUMBER , " & _
                   " PATIENT_ID, " & _
                   " PATIENTTYPE," & _
                   " FIRSTNAME," & _
                   " LASTNAME," & _
                   " PATIENTAGE," & _
                   " EXAMINEPART, " & _
                   " START_REGISTERDATE ," & _
                   " CASE WHEN isnumeric(ACCESSIONNUMBER) = 1 THEN CONVERT(float,ACCESSIONNUMBER) END AS ACCNO " & _
                   " FROM RIS_STUDIES  " & _
                   " WHERE EXAMINEDATE ='" & getdate & "' ORDER BY  ACCNO  DESC "

        DS = Mssql1.GetMSSQLDataset(Strsql, "DS_RIS_STUDIES", "RIS_STUDIES")

        DataGridWorklist.DataSource = DS.Tables(0)

    End Sub


    Private Sub cmdOrderSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOrderSearch.Click

        If txtOrder.Text = "" Then
            MsgBox("กรุณากรอกข้อมูลที่ต้องการค้นหา", , "ข้อมูลไม่ถูกต้อง")
            Exit Sub
        End If


        '*** DataTable ***' 
        Dim DS As New DataSet
        Dim getdate As String = MsDate(Date.Now)
        Strsql = "SELECT " & _
                  " ACCESSIONNUMBER , " & _
                  " PATIENT_ID, " & _
                  " PATIENTTYPE," & _
                  " FIRSTNAME," & _
                  " LASTNAME," & _
                  " PATIENTAGE," & _
                  " START_REGISTERDATE, " & _
                  " EXAMINEPART, " & _
                  " ADMITTINGDATE " & _
                  " FROM RIS_STUDIES  " & _
                  " WHERE ACCESSIONNUMBER ='" & txtOrder.Text & "' ORDER BY ACCESSIONNUMBER DESC "

        DS = Mssql1.GetMSSQLDataset(Strsql, "DS_RIS_STUDIES", "RIS_STUDIES")

        If DS.Tables(0).Rows.Count > 0 Then

            txtOrder.Text = DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString()
            txtHN.Text = DS.Tables(0).Rows(0).Item("PATIENT_ID").ToString()
            txtName.Text = DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString()
            txtType.Text = DS.Tables(0).Rows(0).Item("EXAMINEPART").ToString
            DateTimePicker2.Value = DS.Tables(0).Rows(0).Item("ADMITTINGDATE").ToString()
        Else
            MsgBox("ไม่พบข้อมูลที่ต้องการค้นหา", , "กรุณาตรวจสอบ")
        End If
    End Sub

    Private Sub cmdDateUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDateUpdate.Click
        Dim StrMssql As String

        If DateTimePicker2.Value.ToString = "" Then
            MsgBox("กรุณาเลือกวันที่", , "ข้อมูลไม่ถูกต้อง")
            Exit Sub
        End If

        Dim getdate As String = MsDate(DateTimePicker1.Value)
        StrMssql = "UPDATE RIS_STUDIES  " & _
                    " SET ADMITTINGDATE ='" & getdate & "' " & _
                    " WHERE ACCESSIONNUMBER = '" & Trim(txtOrder.Text) & "' "
        Mssql1.MsExecute(StrMssql)


    End Sub

    Private Sub cmdCSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCSearch.Click
        If txtCOrder.Text = "" Then
            MsgBox("กรุณากรอกข้อมูลที่ต้องการค้นหา", , "ข้อมูลไม่ถูกต้อง")
            Exit Sub
        End If


        '*** DataTable ***' 
        Dim DS As New DataSet
        Dim getdate As String = MsDate(Date.Now)
        Strsql = "SELECT " & _
                  " ACCESSIONNUMBER , " & _
                  " PATIENT_ID, " & _
                  " PATIENTTYPE," & _
                  " FIRSTNAME," & _
                  " LASTNAME," & _
                  " PATIENTAGE," & _
                  " START_REGISTERDATE, " & _
                  " EXAMINEPART, " & _
                  " ADMITTINGDATE " & _
                  " FROM RIS_STUDIES  " & _
                  " WHERE ACCESSIONNUMBER ='" & txtCOrder.Text & "' ORDER BY ACCESSIONNUMBER DESC "

        DS = Mssql1.GetMSSQLDataset(Strsql, "DS_RIS_STUDIES", "RIS_STUDIES")

        If DS.Tables(0).Rows.Count > 0 Then

            txtCOrder.Text = DS.Tables(0).Rows(0).Item("ACCESSIONNUMBER").ToString()
            txtCHN.Text = DS.Tables(0).Rows(0).Item("PATIENT_ID").ToString()
            txtCName.Text = DS.Tables(0).Rows(0).Item("FIRSTNAME").ToString()
            txtCType.Text = DS.Tables(0).Rows(0).Item("EXAMINEPART").ToString
            DateTimePicker3.Value = DS.Tables(0).Rows(0).Item("ADMITTINGDATE").ToString()
        Else
            MsgBox("ไม่พบข้อมูลที่ต้องการค้นหา", , "กรุณาตรวจสอบ")
        End If
    End Sub

    Private Sub cmdDeleteOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDeleteOrder.Click
        If txtCOrder.Text = "" Then
            MsgBox("กรุณากรอก Order ที่ต้องการลบ", , "ข้อมูลไม่ถูกต้อง")
            Exit Sub
        End If
        If txtCHN.Text = "" Then
            MsgBox("กรุณากรอก Order ที่ต้องการลบแล้วลิกปุ่มค้นหา", , "ข้อมูลไม่ถูกต้อง")
            Exit Sub
        End If
        If MsgBox("คุณต้องการลบ Order = " & txtCOrder.Text, vbYesNo, "ยืนยันรายการ") = vbYes Then
            Dim getdate As String = MsDate(DateTimePicker1.Value)
            StrMssql = "DELETE RIS_STUDIES  " & _
                       " WHERE ACCESSIONNUMBER = '" & Trim(txtCOrder.Text) & "' "
            Mssql1.MsExecute(StrMssql)

            StrMssql = "DELETE xn2acc  " & _
                      " WHERE xn = '" & Trim(txtCOrder.Text) & "' "
            Mssql1.MsExecute(StrMssql)

            Strsql = "DELETE xray_pack  " & _
                    " WHERE xray_report_xn = '" & Trim(txtCOrder.Text) & "' "
            Mysql1.MsExecute(Strsql)
            '---------
            DaySrearch()
            '--------
        Else
            Exit Sub
        End If
    End Sub

    Private Sub btnThRoman_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnThRoman.Click
        Dim s As String = InputBox("Please input your name")
        MsgBox(Me.TH2R(s))
    End Sub
    Shared Function EncodeString(ByRef SourceData As String, ByRef CharSet As String) As Byte()
        'get a byte pointer To the source data
        Dim bSourceData As Byte() = System.Text.Encoding.Unicode.GetBytes(SourceData)

        'get destination encoding 
        Dim OutEncoding As System.Text.Encoding = System.Text.Encoding.GetEncoding(CharSet)

        'Encode the data To destination code page/charset
        Return System.Text.Encoding.Convert(System.Text.Encoding.Unicode, OutEncoding, bSourceData)
    End Function

    Public utf8 As Encoding = Encoding.UTF8
    Public ascii As Encoding = Encoding.Default
    Public latin1 As Encoding = Encoding.Unicode


    Public Function Utf8ToAscii(ByVal DataString As String) As String
        If DataString = "" Then Return "" Else 
        Utf8ToAscii = utf8.GetString(ascii.GetBytes(DataString))

    End Function

    Public Function AsciiToUtf8(ByVal DataString As String) As String
        If DataString = "" Then Return "" Else 
        AsciiToUtf8 = ascii.GetString(utf8.GetBytes(DataString))

    End Function
    Public Function AsciiToUnicode(ByVal DataString As String) As String
        If DataString = "" Then Return "" Else 
        'AsciiToUnicode = ascii.GetString(utf8.GetBytes(DataString))
        AsciiToUnicode = ascii.GetString(Encoding.Unicode.GetBytes(DataString))


    End Function
    Function AscEncode(ByVal str) As String
        Dim i
        Dim sAscii

        sAscii = ""
        For i = 1 To Len(str)
            sAscii = sAscii + CStr(Hex(Asc(Mid(str, i, 1))))
        Next

        AscEncode = sAscii
    End Function


    Private Sub txtT2U_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtT2U.Click
        Dim Data As String
        txtDataUtf8.Text = ""
        txtUtf8.Text = ""
        Data = txtTis620.Text

        txtDataUtf8.Text = AsciiToUtf8(Data)
        txtUtf8.Text = txtDataUtf8.Text
    End Sub


    Private Sub txtU2T_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtU2T.Click
        Dim Data As String
        txtDataTis620.Text = ""
        Data = txtUtf8.Text

        txtDataTis620.Text = Utf8ToAscii(Data)

    End Sub
    Private Sub cmdT2Uni_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdT2Uni.Click
        Dim Data As String
        txtUnicode.Text = ""
        Data = txtAscii.Text

        txtUnicode.Text = AsciiToUnicode(Data)
    End Sub

    Private Function UText(ByVal unicodeString As String) As String
        ' The encoding.
        Dim uni As New UnicodeEncoding(), rtn As String = ""

        ' Create a string that contains Unicode characters.

        ' Encode the string.
        Dim encodedBytes As Byte() = uni.GetBytes(unicodeString)

        Dim b As Byte
        For Each b In encodedBytes
            'Console.Write("[{0}]", b)
            rtn = rtn & Format(b, "[{0}]")
        Next b

        UText = rtn
    End Function

    Private Function LicenseChk() As String
        Dim LicenseCode As String
        Dim hospcode As String
        Dim strSql1 As String
        Dim DS As New DataSet
        Dim key As String

        LicenseCode = ClsSQLhelper.License
        key = "sys11266"

        strSql1 = "select  sys_value   from sys_var  where sys_name = 'PCU_MAIN_HOSPCODE' "
        DS = Mysql1.GetMYSQLDataset(strSql1, "ds_sys_var", "sys_var")

        If DS.Tables(0).Rows.Count = 0 Then
            hospcode = ""
        Else

            hospcode = DS.Tables(0).Rows(0).Item(0).ToString

        End If

        If LicenseCode = "0" Then
            'frmLicense.txtGenCode.Text = ClsSQLhelper.License
            'frmLicense.Show()
        Else
            If hospcode <> Crypto.Decrypt(LicenseCode, key) Then
                MsgBox("License ใช้งานไม่ถูกต้อง กรุณาตรวจสอบอีกครั้ง", , "License ")
                'Me.Hide()
                'frmLicense.Show()
                End
            End If
        End If

    End Function

    Private Sub frmBroker_MaximizedBoundsChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.MaximizedBoundsChanged

    End Sub


    Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click
        frmAbout.Show()
    End Sub

    Private Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick
        lblTime.Text = DateTime.Now.ToLongTimeString()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim WebIP As String = ClsSQLhelper.WebBroker

        'System.Diagnostics.Process.Start("http://" & WebIP & "/pkg_pacs/external_interface.aspx")
        System.Diagnostics.Process.Start("http://" & WebIP)
        'using the start method of system.diagnostics.process class
        'process class gives access to local and remote processes

    End Sub
    ' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click         Shell("C:\Program Files\Internet Explorer\iexplore.exe http://www.yourwebsite.com")     End Sub    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    '    Shell("C:\Program Files\Internet Explorer\iexplore.exe http://www.yourwebsite.com")

    'End Sub

    Private Function vbUnicode() As VbStrConv
        Throw New NotImplementedException
    End Function



    Private Sub MainToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs)

    End Sub
End Class